{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Lesson 1 Demo 1: Creating a Table with PostgreSQL\n",
    "\n",
    "Image title: Postgres Icon"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Walk through the basics of PostgreSQL:<br><li>Creating a table <li>Inserting rows of data, <li>Running a simple SQL query to validate the information. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Typically, we would use a python wrapper called *psycopg2* to run the PostgreSQL queries. This library should be preinstalled but in the future to install this library, run the following command in the notebook to install locally: \n",
    "!pip3 install --user psycopg2\n",
    "#### More documentation can be found here: http://initd.org/psycopg/ "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Import the library \n",
    "Note: An error might popup after this command has executed. Read it carefully before proceeding."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a connection to the database\n",
    "1. Connect to the local instance of PostgreSQL (*127.0.0.1*)\n",
    "2. Use the database/schema from the instance. \n",
    "3. The connection reaches out to the database (*studentdb*) and uses the correct privileges to connect to the database (*user and password = student*).\n",
    "\n",
    "### Note 1: This block of code will be standard in all notebooks. \n",
    "### Note 2: Adding the try except will make sure errors are caught and understood"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try: \n",
    "    conn = psycopg2.connect(\"host=127.0.0.1 dbname=studentdb user=student password=student\")\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Could not make connection to the Postgres database\")\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Use the connection to get a cursor that can be used to execute queries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try: \n",
    "    cur = conn.cursor()\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Could not get curser to the Database\")\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Use automactic commit so that each action is commited without having to call conn.commit() after each command. The ability to rollback and commit transactions is a feature of Relational Databases. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.set_session(autocommit=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test the Connection and Error Handling Code\n",
    "The try-except block should handle the error: We are trying to do a select * on a table but the table has not been created yet."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try: \n",
    "    cur.execute(\"select * from udacity.music_library\")\n",
    "except psycopg2.Error as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a database to work in "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try: \n",
    "    cur.execute(\"create database udacity\")\n",
    "except psycopg2.Error as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Close our connection to the default database, reconnect to the Udacity database, and get a new cursor."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try: \n",
    "    conn.close()\n",
    "except psycopg2.Error as e:\n",
    "    print(e)\n",
    "  \n",
    "try: \n",
    "    conn = psycopg2.connect(\"host=127.0.0.1 dbname=studentdb user=student password=student\")\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Could not make connection to the Postgres database\")\n",
    "    print(e)\n",
    "    \n",
    "try: \n",
    "    cur = conn.cursor()\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Could not get curser to the Database\")\n",
    "    print(e)\n",
    "\n",
    "conn.set_session(autocommit=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### We will create a Music Library of albums. Each album has a lot of information we could add to the music library table. We will  start with album name, artist name, year. \n",
    "`Table Name: music_library\n",
    "column 1: Album Name\n",
    "column 2: Artist Name\n",
    "column 3: Year `\n",
    "### Translate this information into a Create Table Statement. \n",
    "\n",
    "Review this document on PostgreSQL datatypes: https://www.postgresql.org/docs/9.5/datatype.html\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try: \n",
    "    cur.execute(\"CREATE TABLE IF NOT EXISTS music_library (album_name varchar, artist_name varchar, year int);\")\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Issue creating table\")\n",
    "    print (e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### No error was found, but lets check to ensure our table was created.  `select count(*)` which should return 0 as no rows have been inserted in the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try: \n",
    "    cur.execute(\"select count(*) from music_library\")\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Issue creating table\")\n",
    "    print (e)\n",
    "    \n",
    "print(cur.fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Insert two rows "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try: \n",
    "    cur.execute(\"INSERT INTO music_library (album_name, artist_name, year) \\\n",
    "                 VALUES (%s, %s, %s)\", \\\n",
    "                 (\"Let It Be\", \"The Beatles\", 1970))\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Inserting Rows\")\n",
    "    print (e)\n",
    "    \n",
    "try: \n",
    "    cur.execute(\"INSERT INTO music_library (album_name, artist_name, year) \\\n",
    "                 VALUES (%s, %s, %s)\", \\\n",
    "                 (\"Rubber Soul\", \"The Beatles\", 1965))\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Inserting Rows\")\n",
    "    print (e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Validate your data was inserted into the table. \n",
    "The while loop is used for printing the results. If executing queries in the Postgres shell, this would not be required."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Note: If you run the insert statement code more than once, you will see duplicates of your data. PostgreSQL allows for duplicates."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try: \n",
    "    cur.execute(\"SELECT * FROM music_library;\")\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: select *\")\n",
    "    print (e)\n",
    "\n",
    "row = cur.fetchone()\n",
    "while row:\n",
    "   print(row)\n",
    "   row = cur.fetchone()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Drop the table to avoid duplicates and clean up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try: \n",
    "    cur.execute(\"DROP table music_library\")\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Dropping table\")\n",
    "    print (e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Close the cursor and connection. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.close()\n",
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}